/* $RCSFile: PortalsDB.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/ADNCRM.Core/DAL/PortalsDB.cs $
 * $log$
 * Revision 6 2010/08/06 19:56:28  christian.surieux
 *   Added Log to All cs files
 */

using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Configuration;
using System.Globalization;

using AddonNice.Security;
using AddonNice.Diagnostics;
using AddonNice.Helpers;
using AddonNice.Settings;

using Trace2    =   System.Diagnostics.Trace;

namespace AddonNice.Configuration
{

    public class DBPortal
    {
        public int PortalId  =   -1;
        public string PortalAlias;
        public string PortalPath;
        public int PortalType  =   0;

        public DBPortal() 
        {}

        public DBPortal(SqlDataReader dr)
        {
            PortalId            =   (int)dr[StStr.DBPortalID];
            PortalAlias         =   (string)dr[StStr.DBPortalAlias];
            PortalType          =   (int)dr[StStr.DBPortalType];
            object ob           =   dr[StStr.DBPortalPath];
            if ( (ob == null) || (ob is DBNull) )
                 PortalPath     =   string.Empty;
            else PortalPath     =   (string)ob;
        }

        public DBPortal(DataRow dr)
        {
            PortalId            =   (int)dr[StStr.DBPortalID];
            PortalAlias         =   (string)dr[StStr.DBPortalAlias];
            PortalType          =   (int)dr[StStr.DBPortalType];
            object ob           =   dr[StStr.DBPortalPath];
            if ( (ob == null) || (ob is DBNull) )
                 PortalPath     =   string.Empty;
            else PortalPath     =   (string)ob;
        }
    }

    /// <summary>
    /// Class that encapsulates all data logic necessary to add/query/delete
    /// Portals within the Portal database.
    /// 
    /// </summary>
    public class PortalsDB :	DBDataStore
    {
        string CRMAdmin                             =   Config.InternalAdminRole + ";";
        string CRMAllUsers                          =   Config.InternallAllUsersRole + ";";

		const string stradn_GetPortals              =   "dbo.adn_GetPortals";
		const string stradn_AddPortalAlias          =   "dbo.adn_AddPortalAlias";
		const string stradn_UpdatePortalAlias       =   "dbo.adn_UpdatePortalAlias";
		const string stradn_DeletePortalAlias       =   "dbo.adn_DeletePortalAlias";
		const string stradn_GetPortalAlias          =   "dbo.adn_GetPortalAlias";
		const string stradn_GetPortalFromAlias      =   "dbo.adn_GetPortalFromAlias";
		const string strGetPortalByPortalID         =   "dbo.adn_GetPortalByPortalID";
		const string strGetPortalCrmServer          =   "dbo.adn_GetPortalCrmServer";
		const string strAddPortal2                  =   "dbo.adn_AddPortal2";
		const string strUpdatePortal2               =   "dbo.adn_UpdatePortal2";
		const string strDeletePortal                =   "dbo.adn_DeletePortal";

        /// <summary>
        /// variant that try to find a specific template connection
        /// </summary>
        public override SqlConnection myConnection
        {
            get
            {
                // Watch if there's a Template's database in the web.config
                // else, use the same database as the portal
                string strSqlConnection = Config.PortalTemplatesConnectionString;
                if (string.IsNullOrEmpty(strSqlConnection))
                    return base.myConnection;

                return new SqlConnection(strSqlConnection);
            }
        }


        public PortalsDB()
        {}

        #region portal loading

        /// <summary>
        /// Main method to load portal parameters, automatically switches sitemap according port used (admin or not)
        /// </summary>
        public SqlDataReader GetPortalSettings(int pageID, string portalAlias,string PortalContentLanguageName,SqlCommand myCommand)
		{
            // We switch SiteMap if not admin portal
            myCommand.CommandType					=   CommandType.StoredProcedure;

            SqlParameter parameterPortalAlias		=   new SqlParameter(StStr.ATPortalAlias, SqlDbType.NVarChar, 128);
			parameterPortalAlias.Value				=   portalAlias;  
			myCommand.Parameters.Add(parameterPortalAlias);

            // input/output
			SqlParameter parameterPageID            =   new SqlParameter(StStr.ATPageID, SqlDbType.Int, 4);
			parameterPageID.Direction			    =   ParameterDirection.InputOutput;
            parameterPageID.Value                   =   pageID;
            myCommand.Parameters.Add(parameterPageID);
			
			SqlParameter parameterPortalLanguage	=   new SqlParameter(StStr.ATPortalLanguage, SqlDbType.NVarChar, 12);
			parameterPortalLanguage.Value			=   PortalContentLanguageName;
			myCommand.Parameters.Add(parameterPortalLanguage);

			// out parameters 
			SqlParameter parameterPortalID          =   new SqlParameter(StStr.ATPortalID, SqlDbType.Int, 4);
			parameterPortalID.Direction				=   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterPortalID);

			SqlParameter parameterPortalName		=   new SqlParameter(StStr.ATPortalName, SqlDbType.NVarChar, 128);
			parameterPortalName.Direction			=   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterPortalName);

			SqlParameter parameterBaseAlias	        =   new SqlParameter(StStr.ATBaseAlias, SqlDbType.NVarChar, 128);
			parameterBaseAlias.Direction			=   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterBaseAlias);

            SqlParameter parameterPortalPath		=   new SqlParameter(StStr.ATPortalPath, SqlDbType.NVarChar, 128);
			parameterPortalPath.Direction			=   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterPortalPath);

            SqlParameter parameterPageName          =   new SqlParameter(StStr.ATPageName, SqlDbType.NVarChar, 50);
            parameterPageName.Direction             =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterPageName);

            SqlParameter parameterPageTitle         =   new SqlParameter(StStr.ATPageTitle, SqlDbType.NVarChar, 200);
            parameterPageTitle.Direction            =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterPageTitle);
            
            SqlParameter parameterPageKeyPhrase     =   new SqlParameter(StStr.ATPageKeyPhrase, SqlDbType.NVarChar, 50);
            parameterPageKeyPhrase.Direction        =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterPageKeyPhrase);

            SqlParameter parameterUrlName           =   new SqlParameter(StStr.ATUrlName, SqlDbType.NVarChar, 50);
            parameterUrlName.Direction              =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterUrlName);

            SqlParameter parameterPortalType	    =   new SqlParameter(StStr.ATPortalType, SqlDbType.Int, 4);
            parameterPortalType.Direction           =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterPortalType);

            SqlParameter parameterPageSkin          =   new SqlParameter(StStr.ATPageSkin, SqlDbType.NVarChar, 50);
            parameterPageSkin.Direction             =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterPageSkin);

            SqlParameter parameterPageOrder         =   new SqlParameter(StStr.ATPageOrder, SqlDbType.Int, 4);
            parameterPageOrder.Direction            =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterPageOrder);

            SqlParameter parameterTabLayout         =   new SqlParameter(StStr.ATTabLayout, SqlDbType.Int, 4);
            parameterTabLayout.Direction            =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterTabLayout);

            SqlParameter parameterNbPanes           =   new SqlParameter(StStr.ATNbPanes, SqlDbType.Int, 4);
            parameterNbPanes.Direction              =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterNbPanes);

            SqlParameter parameterWidth0            =   new SqlParameter(StStr.ATWidth0, SqlDbType.Int, 4);
            parameterWidth0.Direction               =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterWidth0);

            SqlParameter parameterWidth1            =   new SqlParameter(StStr.ATWidth1, SqlDbType.Int, 4);
            parameterWidth1.Direction               =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterWidth1);

            SqlParameter parameterWidth2            =   new SqlParameter(StStr.ATWidth2, SqlDbType.Int, 4);
            parameterWidth2.Direction               =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterWidth2);

            SqlParameter parameterWidth3            =   new SqlParameter(StStr.ATWidth3, SqlDbType.Int, 4);
            parameterWidth3.Direction               =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterWidth3);

            SqlParameter parameterWidth4            =   new SqlParameter(StStr.ATWidth4, SqlDbType.Int, 4);
            parameterWidth4.Direction               =   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterWidth4);


			SqlParameter parameterParentPageID		=   new SqlParameter(StStr.ATParentPageID, SqlDbType.Int, 4);
            parameterParentPageID.Direction         =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterParentPageID);

			SqlParameter parameterAuthRoles			=   new SqlParameter(StStr.ATAuthRoles, SqlDbType.NVarChar, 512);
			parameterAuthRoles.Direction			=   ParameterDirection.Output;
			myCommand.Parameters.Add(parameterAuthRoles);

			SqlDataReader result	                =	null;
			try
			{
				// Open the database connection and execute the command
				myConnection.Open();
				result							    =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
            catch (Exception sqex)
            {
                string msg                          =   string.Format("[E]PortalsDB GetPortalSettings PortalAlias: '{0}', PageId: {1}, ex: {2}", portalAlias,pageID,sqex);
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( msg,DBTraceSwitch.Sw.Info);
                bool nocnx                          =   false;
                if (myConnection.State != System.Data.ConnectionState.Open)
                    nocnx                           =   true;
                if (result != null && !result.IsClosed)
                    result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                if (sqex is SqlException )
                    throw new DatabaseUnreachableException(msg,!nocnx, sqex);
                throw;
            }
            return result;
		}


        #endregion portal loading		
        
        #region portal custom settings

        /// <summary>
        /// All portal settings
        /// </summary>
        public SqlDataReader GetPortalCustomSettings(int templateID)
        {
            SqlConnection theConnection     =   newConnection;

            SqlCommand myCommand			=   new SqlCommand("dbo.adn_GetPortalCustomSettings", theConnection);
            myCommand.CommandType			=   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,templateID);

            SqlDataReader result			=   null;
            try
            {
                theConnection.Open();
                result						=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetPortalCustomSettings ex: {0}", exp),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
                throw;
            }
            return result;
        }

        /// <summary>
        /// One special setting by portalalias
        /// </summary>
        public string GetPortalSingleCustomSettingsByAlias(string portalAlias,string settingName) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]PortalDB GetPortalSingleCustomSettingsByAlias portalAlias: {0}, settingName: {1}", portalAlias,settingName),DBTraceSwitch.Sw.Info);
            SqlConnection theCnx                =   newConnection;
			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetPortalSingleCustomSettingsByAlias", theCnx);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterPortalAlias   =   new SqlParameter(StStr.ATPortalAlias, SqlDbType.NVarChar,50);
            parameterPortalAlias.Value          =   portalAlias;
            myCommand.Parameters.Add(parameterPortalAlias);

            SqlParameter parameterSettingName   =   new SqlParameter(StStr.ATSettingName, SqlDbType.NVarChar,50);
            parameterSettingName.Value          =   settingName;
            myCommand.Parameters.Add(parameterSettingName);

            SqlParameter parameterSettingValue  =   new SqlParameter(StStr.ATSettingValue, SqlDbType.NVarChar,1500);
            parameterSettingValue.Direction      =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterSettingValue);

            string result                       =   string.Empty;
			try
			{
				theCnx.Open();
				myCommand.ExecuteNonQuery();
                result                          =   (string)parameterSettingValue.Value;
 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalDB GetPortalSingleCustomSettingsByAlias ex: {0}",ex),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
				if ( theCnx.State == System.Data.ConnectionState.Open )
					theCnx.Close();
			}
            return result;
        }
        
        /// <summary>
        /// Read one portal setting for dedicated  portalid
        /// </summary>
        public string GetPortalSingleCustomSettings(int PortalID,string settingName) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]PortalDB adn_GetPortalSingleCustomSettings PortalID: {0}, settingName: {1}", PortalID,settingName),DBTraceSwitch.Sw.Info);
            SqlConnection theCnx                =   newConnection;
			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetPortalSingleCustomSettings", theCnx);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,PortalID);
            
            SqlParameter parameterSettingName   =   new SqlParameter(StStr.ATSettingName, SqlDbType.NVarChar,50);
            parameterSettingName.Value          =   settingName;
            myCommand.Parameters.Add(parameterSettingName);

            SqlParameter parameterSettingValue  =   new SqlParameter(StStr.ATSettingValue, SqlDbType.NVarChar,1500);
            parameterSettingValue.Direction     =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterSettingValue);

            string result                       =   string.Empty;
			try
			{
				theCnx.Open();
				myCommand.ExecuteNonQuery();
                result                          =   (string)parameterSettingValue.Value;
 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalDB adn_GetPortalSingleCustomSettings ex: {0}",ex),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
				if ( theCnx.State == System.Data.ConnectionState.Open )
					theCnx.Close();
			}
            return result;
        }
        #endregion portal custom settings		
        
		
        #region portal alias and associations
        
        #region portal list management


        public DataTable GetPortalsTb()
		{
            SqlCommand myCommand        =   new SqlCommand(stradn_GetPortals, myConnection);
            myCommand.CommandType	    =   CommandType.StoredProcedure;
			SqlDataAdapter myAdapt		=   new SqlDataAdapter(myCommand);

			DataTable myDataTb          =   new DataTable();
			try
			{
				myAdapt.Fill(myDataTb);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetPortalsTb ex: {0}",exp),DBTraceSwitch.Sw.Info);
                throw;
			}
			finally
			{
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
			}
			return myDataTb;
		}

        /// <summary>
        /// The GetPortals method returns a SqlDataReader containing all of the
        /// Portals registered in this database.
        /// </summary>
        public SqlDataReader GetPortals()
		{
            SqlCommand myCommand	    =   new SqlCommand(stradn_GetPortals, myConnection);

            myCommand.CommandType		=   CommandType.StoredProcedure;
			SqlDataReader result		=	null;
			try
			{
				myConnection.Open();
				result					=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetPortals ex: {0}",exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
			}
            // Return the datareader 
            return result;
        }

        /// <summary>
        /// Returns a SqlDataReader containing all of the portals, they will be used as templates
        /// </summary>
        [Obsolete("No more used ?")]
        public SqlDataReader GetTemplates()
        {
            SqlCommand myCommand	=   new SqlCommand(stradn_GetPortals, myConnection);
            myCommand.CommandType	=   CommandType.StoredProcedure;

			SqlDataReader result	=	null;
			try
			{
				myConnection.Open();
				result              =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetTemplates ex: {0}",exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
			}
            return result;
        }

        #endregion portal list management

        #region individual portal management
        
        /// <summary>
        /// Used only when editing portal, read only the alias name
        /// </summary>
        public SqlDataReader GetPortal(int portalID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]PortalDB GetPortal PortalID: {0}", portalID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			=   new SqlCommand(strGetPortalByPortalID, myConnection);
			myCommand.CommandType			=   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,portalID);

			SqlDataReader result			=	null;
			try
			{
				myConnection.Open();
				result                      =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalDB GetPortal PortalID: {0}, ex: {1}", portalID, ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public DataTable GetPortalTb(int portalID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]PortalDB GetPortalTb PortalID: {0}", portalID),DBTraceSwitch.Sw.Info);

            SqlCommand myCommand			=   new SqlCommand(strGetPortalByPortalID, myConnection);
            myCommand.CommandType			=   CommandType.StoredProcedure;
			SqlDataAdapter myAdapt			=   new SqlDataAdapter(myCommand);

            AddPortalIDParameters(myCommand,portalID);

            DataTable myDataTb              =   new DataTable();
			try
			{
				myAdapt.Fill(myDataTb);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetPortalTb ex: {0}",exp),DBTraceSwitch.Sw.Info);
                throw;
			}
			finally
			{
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
			}
			return myDataTb;
        }

        public DBPortal GetPortalObj(int portalID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]PortalDB GetPortalObj PortalID: {0}", portalID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			=   new SqlCommand(strGetPortalByPortalID, myConnection);
			myCommand.CommandType			=   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,portalID);

			SqlDataReader dr			    =	null;
			DBPortal result			        =	null;
			try
			{
				myConnection.Open();
				dr                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
                if ( dr.Read() )
                    result                  =   new DBPortal(dr);
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalDB GetPortalObj PortalID: {0}, ex: {1}", portalID, ex),DBTraceSwitch.Sw.Info);
				throw;
            }
            finally
            {
				if ( dr != null && !dr.IsClosed )
					dr.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return result;
        }

        /// <summary>
        /// Read the portalAlias and its associated Crm server info
        /// </summary>
        public SqlDataReader GetPortalCrmServer(int portalID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]PortalDB GetPortalSettings PortalID: {0}", portalID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			=   new SqlCommand(strGetPortalCrmServer, myConnection);
			myCommand.CommandType			=   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,portalID);

			SqlDataReader result			=	null;
			try
			{
				myConnection.Open();
				result                      =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalDB GetPortalCrmServer PortalID: {0}, ex: {1}", portalID, ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        /// <summary>
        /// Add a new portal.
        /// </summary>
        public int AddPortal2(string portalAlias, string portalName, string portalPath, int PortalType)
        {
            SqlCommand myCommand				=   new SqlCommand(strAddPortal2, myConnection);
            myCommand.CommandType				=   CommandType.StoredProcedure;
            SqlParameter parameterPortalAlias	=   new SqlParameter(StStr.ATPortalAlias, SqlDbType.NVarChar, 128);
            parameterPortalAlias.Value			=   portalAlias;
            myCommand.Parameters.Add(parameterPortalAlias);

            SqlParameter parameterPortalName	=   new SqlParameter(StStr.ATPortalName, SqlDbType.NVarChar, 128);
            parameterPortalName.Value			=   portalName;
            myCommand.Parameters.Add(parameterPortalName);

			string pd							=   Config.PortalsDirectory;
			if (portalPath.IndexOf (pd) > -1)
				portalPath						=   portalPath.Substring(portalPath.IndexOf (pd) + pd.Length);
			
			SqlParameter parameterPortalPath	=   new SqlParameter(StStr.ATPortalPath, SqlDbType.NVarChar, 128);
            parameterPortalPath.Value			=   portalPath;
            myCommand.Parameters.Add(parameterPortalPath);

            SqlParameter parameterPortalType	=   new SqlParameter(StStr.ATPortalType, SqlDbType.Int, 4);
            parameterPortalType.Value           =   PortalType;
            myCommand.Parameters.Add(parameterPortalType);

            SqlParameter parameterPortalID		=   new SqlParameter(StStr.ATPortalID, SqlDbType.Int, 4);
            parameterPortalID.Direction			=   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterPortalID);
			int ret								=	-1;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				ret								=	(int)parameterPortalID.Value;
			}
			catch(Exception exp)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB AddPortal2 portalAlias: {0}, ex: {1}",portalAlias,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
        }		

        public void UpdatePortal2(int portalID, string portalName, string portalPath,int PortalType) 
        {
			if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB UpdatePortal2 portalID: {0}, portalName: '{1}', portalPath: '{2}', PortalType: '{3}'",
                    new object[]{portalID,portalName,portalPath,PortalType}),DBTraceSwitch.Sw.Info);
            SqlCommand myCommand				=   new SqlCommand(strUpdatePortal2, myConnection);
            myCommand.CommandType				=   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,portalID);

            SqlParameter parameterPortalName	=   new SqlParameter(StStr.ATPortalName, SqlDbType.NVarChar, 128);
            parameterPortalName.Value			=   portalName;
            myCommand.Parameters.Add(parameterPortalName);

			string pd							=   Config.PortalsDirectory;
			if( pd != null )
			{
				if (portalPath.IndexOf(pd) > -1)
					portalPath					=   portalPath.Substring(portalPath.IndexOf(pd) + pd.Length);
			}

			SqlParameter parameterPortalPath	=   new SqlParameter(StStr.ATPortalPath, SqlDbType.NVarChar, 128);
            parameterPortalPath.Value			=   portalPath;
            myCommand.Parameters.Add(parameterPortalPath);

            SqlParameter parameterPortalType	=   new SqlParameter(StStr.ATPortalType, SqlDbType.Int, 4);
            parameterPortalType.Value           =   PortalType;
            myCommand.Parameters.Add(parameterPortalType);

            try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB UpdatePortal2 ex: {0}", exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
        }
        
        /// <summary>
        /// Removes portal from database. All pages, modules and data wil be removed.
        /// </summary>
        public void DeletePortal(int portalID) 
        {
            SqlCommand myCommand			=   new SqlCommand(strDeletePortal, myConnection);
            myCommand.CommandType			=   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,portalID);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB DeletePortal portalID : {0}, ex: {1}",portalID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}
        
        #endregion individual portal management

        #region Portals Alias 

        /// <summary>
        /// The AddPortalAlias method add a new portal Alias.<br />
        /// </summary>
        public void AddPortalAlias(int portalID,string portalAlias) 
        {
            SqlCommand myCommand				=   new SqlCommand(stradn_AddPortalAlias, myConnection);
            myCommand.CommandType				=   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,portalID);

            SqlParameter parameterPortalAlias	=   new SqlParameter(StStr.ATPortalAlias, SqlDbType.NVarChar, 128);
            parameterPortalAlias.Value			=   portalAlias;
            myCommand.Parameters.Add(parameterPortalAlias);

            try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB AddPortalAlias: {0}, ex: {1}",portalAlias,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
        }		
        
        /// <summary>
        /// Update portal alias in database
        /// </summary>
        public void UpdatePortalAlias(int portalID,string oldPortalAlias,string portalAlias) 
        {
            SqlCommand myCommand			        =   new SqlCommand(stradn_UpdatePortalAlias, myConnection);
            myCommand.CommandType			        =   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,portalID);

            SqlParameter parameterOldPortalAlias	=   new SqlParameter(StStr.ATOldPortalAlias, SqlDbType.NVarChar, 128);
            parameterOldPortalAlias.Value			=   oldPortalAlias;
            myCommand.Parameters.Add(parameterOldPortalAlias);

            SqlParameter parameterPortalAlias	    =   new SqlParameter(StStr.ATPortalAlias, SqlDbType.NVarChar, 128);
            parameterPortalAlias.Value			    =   portalAlias;
            myCommand.Parameters.Add(parameterPortalAlias);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB UpdatePortalAlias portalID: {0}, ex: {1}",portalID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}
        
        /// <summary>
        /// Removes portal alias from database
        /// </summary>
        public void DeletePortalAlias(int portalID,string portalAlias) 
        {
            SqlCommand myCommand			    =   new SqlCommand(stradn_DeletePortalAlias, myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,portalID);

            SqlParameter parameterPortalAlias	=   new SqlParameter(StStr.ATPortalAlias, SqlDbType.NVarChar, 128);
            parameterPortalAlias.Value			=   portalAlias;
            myCommand.Parameters.Add(parameterPortalAlias);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB DeletePortalAlias portalID: {0}, ex: {1}",portalID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}
        
        /// <summary>
        /// All existing alias for one portal
        /// </summary>
		public DataTable GetPortalAliasTb(int portalID)
		{
            SqlCommand myCommand			=   new SqlCommand(stradn_GetPortalAlias, myConnection);
            myCommand.CommandType			=   CommandType.StoredProcedure;
			SqlDataAdapter myAdapt			=   new SqlDataAdapter(myCommand);

            AddPortalIDParameters(myCommand,portalID);

            DataTable myDataTb              =   new DataTable();
			try
			{
				myAdapt.Fill(myDataTb);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetPortalAliasTb ex: {0}",exp),DBTraceSwitch.Sw.Info);
                throw;
			}
			finally
			{
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
			}
			return myDataTb;
		}

        /// <summary>
        /// All existing alias for one portal
        /// </summary>
        public SqlDataReader GetPortalAlias(int portalID)
		{
            SqlCommand myCommand			    =   new SqlCommand(stradn_GetPortalAlias, myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;
            
            AddPortalIDParameters(myCommand,portalID);

            SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result						    =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetPortalAlias PortalID: {0}, ex: {1}",portalID,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
			}
            return result;
        }

        /// <summary>
        /// returns all alias for one portalID
        /// </summary>
        public int GetPortalFromAlias(string portalAlias)
		{
            SqlCommand myCommand			    =   new SqlCommand(stradn_GetPortalFromAlias, myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterPortalAlias	=   new SqlParameter(StStr.ATPortalAlias, SqlDbType.NVarChar, 128);
            parameterPortalAlias.Value			=   portalAlias;
            myCommand.Parameters.Add(parameterPortalAlias);
            
            SqlParameter retParam               =   new SqlParameter("@ReturnValue", SqlDbType.Int); 
            retParam.Direction                  =   ParameterDirection.ReturnValue;
            myCommand.Parameters.Add(retParam);

            int portalID                        =   -1;
            try
			{
                myConnection.Open();
				myCommand.ExecuteNonQuery();
                portalID                        =   (int)retParam.Value;
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetPortalFromAlias PortalAlias: {0}, ex: {1}",portalAlias,exp),DBTraceSwitch.Sw.Info);
                throw;
			}
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
            return portalID;
        }

        #endregion Portals Alias

        #endregion portal alias and associations

        #region Portal utilities

        public SqlDataReader GetTemplateModuleDefinitions(int templateID)
        {
            SqlCommand myCommand			=   new SqlCommand("dbo.adn_GetCurrentModuleDefinitions", myConnection);

            myCommand.CommandType			=   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,templateID);

            SqlDataReader result			=   null;
            try
            {
                myConnection.Open();
                result                      =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetTemplateModuleDefinitions ex: {0}", exp),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if ( myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                throw;
            }
            return result;
        }

        public Guid GetGeneralModuleDefinitionByName(string moduleName)
        {
            SqlConnection theConnection         =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection		            =   new SqlConnection(Config.ConnectionString);

            SqlCommand myCommand				=   new SqlCommand("dbo.adn_GetGeneralModuleDefinitionByName", theConnection);

            myCommand.CommandType				=   CommandType.StoredProcedure;

            SqlParameter parameterFriendlyName	=   new SqlParameter(StStr.ATFriendlyName, SqlDbType.NVarChar, 128);
            parameterFriendlyName.Value			=   moduleName;
            myCommand.Parameters.Add(parameterFriendlyName);
            
            //output
            SqlParameter parameterModuleID		=   new SqlParameter(StStr.ATModuleID, SqlDbType.UniqueIdentifier);
            parameterModuleID.Direction			=   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterModuleID);

            try
            {
                theConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetGeneralModuleDefinitionByName ex: {0}, moduleName {1}", exp, moduleName),DBTraceSwitch.Sw.Info);
                throw;
            }
			finally
			{
                if (theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
			}
            try
            {
                return (Guid)parameterModuleID.Value;
            }
            catch (Exception ex)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetGeneralModuleDefinitionByName ex: {0}, moduleName: {1}, ModuleID: {2}", ex,moduleName, parameterModuleID.Value),DBTraceSwitch.Sw.Info);
                throw;
            }
        }

        public SqlDataReader GetPagesByPortal(int templateID)
        {
            SqlConnection theConnection                 =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection		                    =   new SqlConnection(Config.ConnectionString);
            SqlCommand myCommand						=   new SqlCommand("dbo.adn_GetTabsByPortal", theConnection);
            myCommand.CommandType						=   CommandType.StoredProcedure;
            
            AddPortalIDParameters(myCommand,templateID);

            SqlDataReader result						=   null;
            try
            {
                theConnection.Open();
                result									=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetPagesByPortal ex: {0}", exp),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
                throw;
            }
            return result;
        }
    
	
		/// <summary>
		/// The UpdatePortalSetting Method updates a single module setting
		/// in the PortalSettings database table.
		/// </summary>
		public void UpdatePortalSetting(int portalID, string key, string value) 
		{
            SqlConnection theConnection         =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection		            =   new SqlConnection(Config.ConnectionString);
            if ( DBTraceSwitch.Sw.TraceVerbose)
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]PortalsDB UpdatePortalSetting BEG PortalID:{0}, key: [{1}], val: [{2}]", portalID,key,value),DBTraceSwitch.Sw.Info);
			
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_UpdatePortalSetting", theConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;
			
            AddPortalIDParameters(myCommand,portalID);

			SqlParameter parameterKey		    =   new SqlParameter("@SettingName", SqlDbType.NVarChar, 50);
			parameterKey.Value				    =   key.ToUpper();
			myCommand.Parameters.Add(parameterKey);
            
			SqlParameter parameterValue		    =   new SqlParameter("@SettingValue", SqlDbType.NVarChar, 1500);
			parameterValue.Value			    =   value;
			myCommand.Parameters.Add(parameterValue);
            
			// Execute the command
			try
			{
				theConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    System.Diagnostics.Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB UpdatePortalSetting : PortalID:{0}, ex {1}", portalID, ex),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( theConnection.State == System.Data.ConnectionState.Open )
					theConnection.Close();
			}
			//Invalidate cache
            PortalSettings.FlushPortalSettingsCache();
		}

	        /// <summary>
		/// Get languages list from Portaldb
		/// </summary>
		public string GetLanguageList(string portalAlias)
		{
			    string langlist                     =   string.Empty;
				SqlCommand myCommand				=   new SqlCommand("dbo.adn_GetPortalSettingsLangList", myConnection);
				myCommand.CommandType				=   CommandType.StoredProcedure;
				SqlParameter parameterPortalAlias	=   new SqlParameter(StStr.ATPortalAlias, SqlDbType.NVarChar, 128);
				parameterPortalAlias.Value			=   portalAlias;  // Specify the Portal Alias Dynamically 
				myCommand.Parameters.Add(parameterPortalAlias);

				try
				{
					myConnection.Open();
					object tmp                      =   myCommand.ExecuteScalar();
					if ( tmp != null ) 
						langlist                    =   tmp.ToString();
				}
				catch(Exception ex)
				{
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]PortalsDB GetLanguageList ex: {0}", ex),DBTraceSwitch.Sw.Info);
                    throw new DatabaseUnreachableException("[E]PortalsDB GetLanguageList from db failed",( myConnection.State == ConnectionState.Open ), ex);
				}
				finally
				{
                    if ( myConnection.State == ConnectionState.Open )
    					myConnection.Close();
				}

			return langlist;
		}
        #endregion Portal utilities
	}
}
